﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using DTO;
using DAO;

namespace ASP.ADO
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ManagerAgency.mdf;Integrated Security=True;User Instance=True");
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT * FROM HoSoDaiLy";
            command.CommandType = CommandType.Text;
            command.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds, "BookIssueDetails");
            if (ds.Tables[0].Rows.Count > 0)
            {
                grdXML.DataSource = ds;
                grdXML.DataBind();
            }

            // Get a FileStream object
            StreamWriter xmlDoc = new StreamWriter(Server.MapPath("~/FileTest/Testdo.xml"), false);
            // Apply the WriteXml method to write an XML document
            ds.WriteXml(xmlDoc);
            xmlDoc.Close();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            StreamReader xmlReader = new StreamReader(Server.MapPath("~/FileTest/Testdo.xml"), false);
            
            ds.ReadXml(xmlReader);
            xmlReader.Close();


            SqlConnection con = new SqlConnection(@"Data Source=TANNGUYEN-LAP\SQLEXPRESS;Initial Catalog=Tan;Integrated Security=True");
            con.Open();


            SqlCommand command = con.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "usp_InsertHoSoDaiLy";

            foreach (DataTable temp in ds.Tables)
            {
                foreach (DataRow dr in temp.Rows)
                {

                    HoSoDaiLyDTO hoSoDaiLyDTO = new HoSoDaiLyDTO();

                    hoSoDaiLyDTO.MaHoSoDaiLy = dr["MaHoSoDaiLy"].ToString();
                    hoSoDaiLyDTO.TenDaiLy = dr["TenDaiLy"].ToString();
                    hoSoDaiLyDTO.MaQuan = dr["MaQuan"].ToString();
                    hoSoDaiLyDTO.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
                    hoSoDaiLyDTO.TienNo = float.Parse(dr["TienNo"].ToString());
                    hoSoDaiLyDTO.DiaChi = dr["DiaChi"].ToString();
                    hoSoDaiLyDTO.DienThoai = dr["DienThoai"].ToString();
                    hoSoDaiLyDTO.EMail = dr["Email"].ToString();
                    hoSoDaiLyDTO.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();

                    List<SqlParameter> sqlParams = new List<SqlParameter>();
                    sqlParams.Add(new SqlParameter("@MaHoSoDaiLy", hoSoDaiLyDTO.MaHoSoDaiLy));
                    sqlParams.Add(new SqlParameter("@TenDaiLy", hoSoDaiLyDTO.TenDaiLy));
                    sqlParams.Add(new SqlParameter("@TienNo", hoSoDaiLyDTO.TienNo));
                    sqlParams.Add(new SqlParameter("@MaLoaiDaiLy", hoSoDaiLyDTO.MaLoaiDaiLy));
                    sqlParams.Add(new SqlParameter("@MaQuan", hoSoDaiLyDTO.MaQuan));
                    sqlParams.Add(new SqlParameter("@NgayTiepNhan", hoSoDaiLyDTO.NgayTiepNhan));
                    sqlParams.Add(new SqlParameter("@EMail", hoSoDaiLyDTO.EMail));
                    sqlParams.Add(new SqlParameter("@DienThoai", hoSoDaiLyDTO.DienThoai));
                    sqlParams.Add(new SqlParameter("@DiaChi", hoSoDaiLyDTO.DiaChi));
                    command.Parameters.Clear();
                    foreach (SqlParameter sqlPar in sqlParams)
                    {
                        command.Parameters.Add(sqlPar);

                    }
                    
                    command.ExecuteNonQuery();
                    
                }

            }
            con.Close();

        }
    }
}